--LuP: naplneni atributu "Pouziti v logisticke manipulacni jednotce ve tride "Polozky dokladu" (NESPOUSTET)
<g>
select A.ic_b as IC, A.u_b as U 
into #tmptable
from a30611012u10 as A
join a30611011u10 as B on (B.ic = A.ic_a and B.u = A.u_a)
left outer join a813084u10212 as C on (C.ic = B.ic_a and C.u = B.u_a)
group by A.ic_b, A.u_b
having COUNT(*) > 0 and COUNT(*) = sum(case when C.ic is null then 0 else 1 end)

update A set a39904717u10 = 1
from a29555u10 as A
join #tmptable as B on (B.IC = A.ic and B.U = A.u)
where A.a39904717u10 is null

drop table #tmptable
</g>

-- TB: naplnění realizátora zodpovědnou osobou u telefonních hovorů (NESPOUSTET)
<g>
  <a>
    -- Fyzická účast na akci
    INSERT INTO "a1583150u10" (ic, u, ic_a, u_a, ic_b, u_b, langstate, ts0, useric, useru, sstate, sstate_a, sstate_b)
    SELECT zs.ic, zs.u, zs.ic_a, zs.u_a, zs.ic_b, zs.u_b, zs.langstate, zs.ts0, zs.useric, zs.useru, zs.sstate, zs.sstate_a, zs.sstate_b
    -- Telefonní hovory
    FROM "a209826u9" as tel
    -- Subjekt zodpovědný za akci
    INNER JOIN "a19531u10" AS zs ON (zs.ic_a = tel.ic) AND (zs.u_a = tel.u)
    -- Realizátor akce
    LEFT JOIN "a1582870u10" AS rel ON (rel.ic = zs.ic) AND (rel.u = zs.u)
    WHERE (zs.ic IS NOT NULL) AND (zs.u IS NOT NULL) AND
          (rel.ic IS NULL) AND (rel.u IS NULL)

    -- Realizátor akce
    INSERT INTO "a1582870u10" (ic, u, ic_a, u_a, ic_b, u_b, langstate, ts0, useric, useru, sstate, sstate_a, sstate_b)
    SELECT zs.ic, zs.u, zs.ic_a, zs.u_a, zs.ic_b, zs.u_b, zs.langstate, zs.ts0, zs.useric, zs.useru, zs.sstate, zs.sstate_a, zs.sstate_b
    -- Telefonní hovory
    FROM "a209826u9" as tel
    -- Subjekt zodpovědný za akci
    INNER JOIN "a19531u10" AS zs ON (zs.ic_a = tel.ic) AND (zs.u_a = tel.u)
    -- Realizátor akce
    LEFT JOIN "a1582870u10" AS rel ON (rel.ic = zs.ic) AND (rel.u = zs.u)
    WHERE (zs.ic IS NOT NULL) AND (zs.u IS NOT NULL) AND
          (rel.ic IS NULL) AND (rel.u IS NULL)
  </a>
  <p>
    -- Fyzická účast na akci
    INSERT INTO "a1583150u10" (ic, u, ic_a, u_a, ic_b, u_b, langstate, ts0, useric, useru)
    SELECT zs.ic, zs.u, zs.ic_a, zs.u_a, zs.ic_b, zs.u_b, zs.langstate, zs.ts0, zs.useric, zs.useru
    -- Telefonní hovory
    FROM "a209826u9" as tel
    -- Subjekt zodpovědný za akci
    INNER JOIN "a19531u10" AS zs ON (zs.ic_a = tel.ic) AND (zs.u_a = tel.u)
    -- Realizátor akce
    LEFT JOIN "a1582870u10" AS rel ON (rel.ic = zs.ic) AND (rel.u = zs.u)
    WHERE (zs.ic IS NOT NULL) AND (zs.u IS NOT NULL) AND
          (rel.ic IS NULL) AND (rel.u IS NULL)

    -- Realizátor akce
    INSERT INTO "a1582870u10" (ic, u, ic_a, u_a, ic_b, u_b, langstate, ts0, useric, useru)
    SELECT zs.ic, zs.u, zs.ic_a, zs.u_a, zs.ic_b, zs.u_b, zs.langstate, zs.ts0, zs.useric, zs.useru
    -- Telefonní hovory
    FROM "a209826u9" as tel
    -- Subjekt zodpovědný za akci
    INNER JOIN "a19531u10" AS zs ON (zs.ic_a = tel.ic) AND (zs.u_a = tel.u)
    -- Realizátor akce
    LEFT JOIN "a1582870u10" AS rel ON (rel.ic = zs.ic) AND (rel.u = zs.u)
    WHERE (zs.ic IS NOT NULL) AND (zs.u IS NOT NULL) AND
          (rel.ic IS NULL) AND (rel.u IS NULL)
  </p>
</g>



-- Doprava: Registrační značky silničních vozidel -> Identifikační prvky (nespoustet)
<g>
	<p>
	insert into a1260198u10 (u, ic, ts0, useric, useru, remotekey, langstate)
	select src.u, src.ic, getdate(), 1105, 0, src.remotekey, src.langstate
	</p>
	<a>
	insert into a1260198u10 (u, ic, ts0, useric, useru, remotekey, langstate, sstate)
	select src.u, src.ic, getdate(), 1105, 0, src.remotekey, src.langstate, 0
	</a>
	from
	  a372245u10212 src
	  left outer join a1260198u10 dst on src.ic = dst.ic and src.u = dst.u
	where
	  dst.ic is null
</g>

-- Doprava: xPIN -> PIN (nespoustet)
<g>
update a1260198u10 set useric = 1105, useru = 0, ts0 = getdate(), a39759107u10 = a382578u10212
where a39759107u10 is null and a382578u10212 is not null
</g>

-- odstraneni vazby "Pusobeni akce na statky" pro prepravni zasilky (nespoustet)
<g>
delete from a645001u10212
delete d from a267941u10 d inner join a813082u10212 z on d.ic_b = z.ic and d.u_b = z.u
</g>
